from common import SongEntity,SearchFilter
import DatabaseConnection
import MySQLdb.cursors

class SongData(object):
    def __init__(self):
        pass

    def InsertSong(self,song):
        ID = None
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsSong',(song.AlbumID,song.Name,song.Visibility))
        ID = cursor.fetchone()[0]
        cursor.close()
        conn.commit()
        conn.close()
        return ID

    def UpdateSong(self,song):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdSong',(song.SongID,song.AlbumID,song.Name,song.Visibility)) 
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteSong(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelSong',(ID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type SongEntity or None (if there is no record with this ID)
    def SelectSongByID(self,ID):
        query = """ SELECT SongID,AlbumID,Name,Visibility,AlbumName,ArtistName,Genre,GenreDesc,PlayCount,RecentPlay,AverageRating
                    FROM vwSong
                    WHERE SongID='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return SongEntity.SongEntity(songID=result[0][0],albumID=result[0][1],name=result[0][2],visibility=result[0][3],albumName=result[0][4],artistName=result[0][5]
                                         ,genre=result[0][6],genreDesc=result[0][7],playCount=result[0][8],recentPlay=result[0][9],averageRating=result[0][10])
        else:
            return None

        return None

    #This method returns a list of objects of type SongEntity
    def SelectSongByFilter(self,searchFilter):
        query = """ SELECT SongID,AlbumID,Name,Visibility,AlbumName,ArtistName,Genre,GenreDesc,PlayCount,RecentPlay,AverageRating
                    FROM vwSong"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(SongEntity.SongEntity(songID=record[0],albumID=record[1],name=record[2],visibility=record[3],albumName=record[4],artistName=record[5]
                                         ,genre=record[6],genreDesc=record[7],playCount=record[8],recentPlay=record[9],averageRating=record[10]))
        return recordSet

    def SelectAllSongs(self):
        query = """ SELECT SongID,AlbumID,Name,Visibility,AlbumName,ArtistName,Genre,GenreDesc,PlayCount,RecentPlay,AverageRating
                    FROM vwSong"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(SongEntity.SongEntity(songID=record[0],albumID=record[1],name=record[2],visibility=record[3],albumName=record[4],artistName=record[5]
                                         ,genre=record[6],genreDesc=record[7],playCount=record[8],recentPlay=record[9],averageRating=record[10]))
        return recordSet

def getSongsAndRatings(albumID):
    query = """ SELECT s.SongID, s.Name, s.Visibility, r.Score
                FROM Song s, Rating r
                WHERE s.SongID = r.SongID
                AND s.AlbumID = %s""" % albumID

    conn = DatabaseConnection.GetConnection()
    
    cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()

    return result
